﻿using StudentProject.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace StudentProject.DAL
{
    public class ClassesServiceDal
    {
        /// <summary>
        /// 查询并显示在DGV中
        /// 查询的是试图，试图里存放的是需要的字段
        /// </summary>
        /// <returns></returns>
        public List<View_ClassesMajor> GetLsit()
        {
            string sql = "select * from View_ClassesMajor";
            //使用helper进行查询，得到结果
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            
            //将dt中的数据转存到list中
            List<View_ClassesMajor> list = new List<View_ClassesMajor>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new View_ClassesMajor()
                {
                    Cid = row["Cid"].ToString(),
                    CName = row["CName"].ToString(),
                    HeadTeacher = row["HeadTeacher"].ToString(),
                    MName = row["MName"].ToString()
                });
            }
            //将集合返回
            return list;
        }

        /// <summary>
        /// 查询并显示在修改的文本框中
        /// 这里是根据Id查询试图
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public View_ClassesMajor LoadClasses(string pid)
        {
            string sql = "select * from View_ClassesMajor where Cid=@pid";
            SqlParameter ps = new SqlParameter("@pid", pid);
            View_ClassesMajor cm = null;
            SqlDataReader dr = SqlHelper.ExecuteReader(sql, ps);
            if (dr.HasRows)//是否有行
            {
                while (dr.Read())
                {
                    cm = new View_ClassesMajor();
                    cm.Cid = dr.GetString(0);
                    cm.CName = dr.GetString(1);
                    cm.HeadTeacher = dr.GetString(2);
                    cm.MName = dr.GetString(3);
                }
            }
            //执行语句并返回结果
            return cm;
        }

        /// <summary>
        /// 添加数据
        /// 这里添加Classes表时，试图也会同时添加
        /// </summary>
        /// <param name="classes"></param>
        /// <returns></returns>
        public int AddClassesInfo(Classes classes)
        {
            //添加的SQL语句
            string sql = "insert into Classes(ID,Name,HeadTeacher,MajorID) values(@ID,@Name,@HeadTeacher,@MajorID)";
            //SQL语句的参数
            SqlParameter[] ps =
            {
                new SqlParameter("@ID",classes.ID),
                new SqlParameter("@Name",classes.Name),
                new SqlParameter("@HeadTeacher",classes.HeadTeacher),
                new SqlParameter("@MajorID",classes.MajorID)
            };
            //返回操作
            return SqlHelper.ExecuteNonQuery(sql, ps);
        }

        /// <summary>
        /// 修改并且保存
        /// 这里修改是修改的classes表，试图哪里会自动修改，
        /// </summary>
        /// <param name="classes"></param>
        /// <returns></returns>
        public int UpdateClassesInfo(Classes classes)
        {
            string sql = "update Classes set Name=@name,MajorID=@majorId,HeadTeacher=@headteacher where ID=@id";
            SqlParameter[] pars =
            {
                new SqlParameter("@id",classes.ID),
                new SqlParameter("@name",classes.Name),
                new SqlParameter("@headteacher",classes.HeadTeacher),
                new SqlParameter("@majorId",classes.MajorID)
            };
            //执行语句并返回结果
            return SqlHelper.ExecuteNonQuery(sql, pars);
        }

        /// <summary>
        /// 删除数据
        /// 删除classes表时，试图会自动地删除
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public int RemoveClasses(string pid)
        {
            //构造删除的sql语句
            string sql = "delete from Classes where ID=@pid";
            //根据语句构造参数
            SqlParameter ps = new SqlParameter("@pid", pid);
            //执行操作
            return SqlHelper.ExecuteNonQuery(sql, ps);
        }

    }
}
